package attend.dao.attend;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.servlet.jsp.jstl.sql.SQLExecutionTag;

import attend.com.CodeMetrix;

import exception.BaseException;
import util.DBConnection;

public class AttendFinishDao extends DBConnection {
	
	private List<HashMap<String, String>> 	list 				= null;		//조회결과를 담는 Collection
	
	private List<String>					listCondition		= null;		//DAO로 넘기는 PARAMETERS
	
	private Connection 			con 							= 	null;
	private PreparedStatement 	pstmt 							= 	null;
	private ResultSet 			rs 								= 	null;
	
	private StringBuffer 		sql 	= 	new StringBuffer(); //sql statement
	
	private String				msg			=	null;
	private String				chk_in_msg	=	"";			//출근정보가 있으면 메시지를 남긴다.
	private String				chk_out_msg = 	"";			//퇴근정보가 있으면 메시지를 남긴다.
	private String				yyyymmdd	=	null;		//휴가입력 기준일을 동적으로 가져옴.
	
	private int					cnt;	//출근정보가 없으면 0 있으면 1이된다.
	
	private int					rowCount;	//total row count

	private String close_dt;
	

	/**
	 * @throws BaseException
	 * @throws SQLException
	 */
	public AttendFinishDao() throws BaseException {	}
	

	/**
	 * <pre>조회건수</pre>
	 * @param s_date
	 * @param e_date
	 * @param identy_yn
	 * @param finish_yn
	 * @return
	 * @throws BaseException
	 */
	public int retrieveCurFinishDTRow(String s_date , String e_date , String identy_yn , String finish_yn ) throws BaseException {
		s_date = s_date.replace("-", "");
		e_date = e_date.replace("-", "");
		
		try {
			con = DBConnection.getDataSource().getConnection();
			
			sql.setLength(0);
			
			sql.append("								SELECT COUNT(*) CNT FROM (																														\n");
			sql.append("								SELECT ROW_NUMBER() OVER(ORDER BY YYYYMMDD DESC) AS ROWNUM , YYYYMMDD , CONFIRM_YN , CASE WHEN ISNULL (CLOSE_DT, 'N') = 'N' THEN 'N' ELSE 'Y' END CLOSE_YN												\n");
			sql.append("								  FROM    (                                                                                         												\n");
			sql.append("														SELECT YYYYMMDD ,                                                           												\n");
			sql.append("								                  CASE                                                                              												\n");
			sql.append("								                     WHEN COUNT (CONFIRM_YN) = 1                                                    												\n");
			sql.append("								                     THEN                                                                           												\n");
			sql.append("								                        CASE WHEN MAX(CONFIRM_YN) = 'Y' THEN 'Y' ELSE 'N' END                       												\n");
			sql.append("								                     ELSE                                                                           												\n");
			sql.append("								                        'N'                                                                         												\n");
			sql.append("								                  END                                                                               												\n");
			sql.append("								                     CONFIRM_YN                                                                     												\n");
			sql.append("								             FROM (                                                                                 												\n");
			sql.append("													SELECT YYYYMMDD , CONFIRM_YN                                   																	\n");
			sql.append("								                     FROM TB_ATTEND X                                                                 												\n");
			sql.append("								                     JOIN TB_EMP E                                                                  												\n");
			sql.append("								                    ON X.EMP_NO = E.EMP_NO AND E.WORK_YN = 'Y' AND YYYYMMDD BETWEEN '" + s_date + "' AND '" + e_date + "'                           \n");
			sql.append("								                   GROUP BY YYYYMMDD , CONFIRM_YN                                                   												\n");
			sql.append("																	 ) A                                                            												\n");
			sql.append("								            GROUP BY YYYYMMDD                                                                       												\n");
			sql.append("													 ) AA                                                                           												\n");
			sql.append("								       LEFT OUTER JOIN TB_ATTEND_CLOSE BB                                                           												\n");
			sql.append("								       ON AA.YYYYMMDD = BB.CLOSE_DT                                                                 												\n");
			sql.append("								       ) Z                                                                																			\n");
			sql.append("								WHERE 1=1                                                                       																			\n");
			sql.append("										AND     CONFIRM_YN 	LIKE REPLACE('" + identy_yn + "' , 'ALL' , '%')																			\n");
			sql.append("										AND 	CLOSE_YN 	LIKE REPLACE('" + finish_yn + "' , 'ALL' , '%')   			                                                            \n");
			

//			System.out.println("======================================\r\n");
//			System.out.println("출력건수\r\n" + sql.toString());
//			System.out.println("======================================\r\n");
			
			pstmt 	= 	con.prepareStatement(sql.toString());
			
//			pstmt.setInt(1 , emp_no);				//사번
//			pstmt.setInt(2 , emp_no);				//사번
//			pstmt.setString(3 , s_date);				//시작 기준일짜
//			pstmt.setString(4 , e_date);				//끝 기준일짜
			
			rs		=	pstmt.executeQuery();
			
			if (rs.next()) {
				rowCount = rs.getInt("CNT");
			}
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt 	!= null) 	pstmt.close();
				if (rs 		!= null) 	rs.close();
				if (con 	!= null) 	con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
		
		return rowCount;
	}
	
	
	/**
	 * @param s_row
	 * @param e_row
	 * @param s_date
	 * @param e_date
	 * @param identy_yn
	 * @param finish_yn
	 * @return List
	 * @throws BaseException
	 */
	public List retrieveCurFinishDT(int s_row , int e_row , String s_date , String e_date , String identy_yn , String finish_yn) throws BaseException {
		
		s_date = s_date.replace("-", "");
		e_date = e_date.replace("-", "");
		
		try {
			con = DBConnection.getDataSource().getConnection();
			
			sql.setLength(0);
			
			sql.append("								SELECT Z.* FROM (																																	\n");		
			sql.append("								SELECT ROW_NUMBER() OVER(ORDER BY YYYYMMDD DESC) AS ROWNUM , YYYYMMDD , CONFIRM_YN , CASE WHEN ISNULL (CLOSE_DT, 'N') = 'N' THEN 'N' ELSE 'Y' END CLOSE_YN												\n");
			sql.append("								  FROM    (                                                                                         												\n");
			sql.append("														SELECT YYYYMMDD ,                                                           												\n");
			sql.append("								                  CASE                                                                              												\n");
			sql.append("								                     WHEN COUNT (CONFIRM_YN) = 1                                                    												\n");
			sql.append("								                     THEN                                                                           												\n");
			sql.append("								                        CASE WHEN MAX(CONFIRM_YN) = 'Y' THEN 'Y' ELSE 'N' END                       												\n");
			sql.append("								                     ELSE                                                                           												\n");
			sql.append("								                        'N'                                                                         												\n");
			sql.append("								                  END                                                                               												\n");
			sql.append("								                     CONFIRM_YN                                                                     												\n");
			sql.append("								             FROM (                                                                                 												\n");
			sql.append("													SELECT YYYYMMDD , CONFIRM_YN                                   																	\n");
			sql.append("								                     FROM TB_ATTEND X                                                                 												\n");
			sql.append("								                     JOIN TB_EMP E                                                                  												\n");
			sql.append("								                    ON X.EMP_NO = E.EMP_NO AND E.WORK_YN = 'Y' AND YYYYMMDD BETWEEN '" + s_date + "' AND '" + e_date + "'                           \n");
			sql.append("								                   GROUP BY YYYYMMDD , CONFIRM_YN                                                   												\n");
			sql.append("																	 ) A                                                            												\n");
			sql.append("								            GROUP BY YYYYMMDD                                                                       												\n");
			sql.append("													 ) AA                                                                           												\n");
			sql.append("								       LEFT OUTER JOIN TB_ATTEND_CLOSE BB                                                           												\n");
			sql.append("								       ON AA.YYYYMMDD = BB.CLOSE_DT                                                                 												\n");
			sql.append("								) Z                                                                																					\n");
			sql.append("								WHERE ROWNUM BETWEEN " + s_row + " AND  " + e_row + "                                                                            					\n");
			sql.append("										AND     CONFIRM_YN 	LIKE REPLACE('" + identy_yn + "' , 'ALL' , '%')																			\n");
			sql.append("										AND 	CLOSE_YN 	LIKE REPLACE('" + finish_yn + "' , 'ALL' , '%')   			                                                            \n");
			
			System.out.println("======================================\r\n");
			System.out.println("\r\n" + sql.toString());
			System.out.println("======================================\r\n");
			
			pstmt 	= 	con.prepareStatement(sql.toString());
			
//			pstmt.setInt(1 , emp_no);				//사번
//			pstmt.setInt(2 , emp_no);				//사번
//			pstmt.setString(3 , s_date);			//시작 기준일짜
//			pstmt.setString(4 , e_date);			//끝 기준일짜
			
			rs		=	pstmt.executeQuery();
			
			list	=	new ArrayList<HashMap<String,String>>();
			
			while (rs.next()) {
				
				HashMap<String, String> hm = new HashMap<String, String>();
				
				hm.put("yyyymmdd"		, rs.getString("YYYYMMDD")	);
				hm.put("confirm_yn"		, rs.getString("CONFIRM_YN"));
				hm.put("close_yn"		, rs.getString("CLOSE_YN")	);
				
				list.add(hm);
			}
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt 	!= null) 	pstmt.close();
				if (rs 		!= null) 	rs.close();
				if (con 	!= null) 	con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
		
		return list;
	}
	
	/**
	 * <pre>마감데이터가 있는지 확인한다.</pre>
	 * @param updateArgs
	 * @author Yang D
	 * @since	2009.04.15
	 */
	public int retrieveFinishDT(String updateArgs) throws BaseException {
		
		try {
			con = DBConnection.getDataSource().getConnection();

			sql.setLength(0);

			sql.append("                     SELECT 	COUNT ( * ) CNT                           				\n");
			sql.append("                       FROM 	TB_ATTEND_CLOSE                           				\n");
			sql.append("                      WHERE 	CLOSE_DT IN ( '' , " + updateArgs + " )                    \n");

//			System.out.println("======================================\r\n");
//			System.out.println(sql.toString());
//			System.out.println("======================================\r\n");

			pstmt 	= con.prepareStatement(sql.toString());

			rs 		= pstmt.executeQuery();

			while (rs.next()) {
				rowCount = rs.getInt("CNT");
			}
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();

			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
		return rowCount;
		
	}

	/**
	 * <pre>근태마감 전 체크</pre>
	 * @throws BaseException
	 * @author 	Choi byoung ock
	 * @since	2011.09.01
	 */
	public String insertFinishCheck(String updateArgs) throws BaseException{
		String[] checkDT	=	updateArgs.split(",");		
		String success = "";
		String fail = "";
		
		try {
			con = DBConnection.getDataSource().getConnection();

			sql.setLength(0);

			sql.append("                     SELECT COUNT(*) CNT                       				\n");
			sql.append("                     FROM TB_ATTEND                           				\n");
			sql.append("                     WHERE CONFIRM_YN = 'N'                    				\n");
			sql.append("                     AND YYYYMMDD = ?               	\n");
		
			pstmt 	= con.prepareStatement(sql.toString());

			for (int i = 0; i < checkDT.length; i++){
				pstmt.setString(1, checkDT[i]);
				rs = pstmt.executeQuery();
				while(rs.next()){
					if (rs.getInt("CNT") == 0) {
						success += checkDT[i].toString()+",";
					} else if (rs.getInt("CNT") > 0 ) {
						fail += checkDT[i].toString()+","; 
					}
				}
			}
			
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();

			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
		return success;
	}

	/**
	 * <pre>근태마감 INSERT</pre>
	 * @throws BaseException
	 * @author 	Yang D
	 * @since	2009.04.15
	 */
	public void insertFinishDT(String updateArgs , String empNo) throws BaseException {
		
		String[] closeDT	=	updateArgs.split(",");
		
		System.out.println("insertFinishDTinsertFinishDTinsertFinishDTinsertFinishDTinsertFinishDTinsertFinishDTinsertFinishDT");
		System.out.println(java.util.Arrays.toString(closeDT));
		System.out.println("insertFinishDTinsertFinishDTinsertFinishDTinsertFinishDTinsertFinishDTinsertFinishDTinsertFinishDT");
		
		try {

			con = DBConnection.getDataSource().getConnection();
			con.setAutoCommit(false);

				
			sql.setLength(0);
			
			sql.append("                     INSERT INTO TB_ATTEND_CLOSE ( CLOSE_DT )                           \n");
			sql.append("                     VALUES ( ? )                                    					\n");

			System.out.println("======================================\r\n");
			System.out.println(sql.toString());
			System.out.println("======================================\r\n");
			
			pstmt = con.prepareStatement(sql.toString());
			
			for (int i = 0; i < closeDT.length; i++) {
				
				pstmt.setString( 1 , closeDT[i]);
				
				pstmt.addBatch();
			}
			
			pstmt.executeBatch();
			
			
			//강제확인
			sql.setLength(0);
			
			sql.append("                     UPDATE TB_ATTEND                           				\n");
			sql.append("                     SET ETC_MGR 		= '관리자마감'                           \n");
			sql.append("                     , APPROV_EMP_NO 	= "+empNo+"                           	\n");
			sql.append("                     , CONFIRM_YN 		= 'Y'                           		\n");
			sql.append("                     , UPDATE_DT 		= GETDATE()                           	\n");
			sql.append("                     WHERE YYYYMMDD 	= ?                           			\n");
			sql.append("                     AND  CONFIRM_YN 	= 'N'                        			\n");
			
			pstmt = con.prepareStatement(sql.toString());
			
			System.out.println("======================================\r\n");
			System.out.println(sql.toString());
			System.out.println("======================================\r\n");
			
			for (int i = 0; i < closeDT.length; i++) {
				
				pstmt.setString( 1 , closeDT[i]);
				
				pstmt.addBatch();
			}
			
			pstmt.executeBatch();
			
			con.commit();

		} catch (SQLException se) {
			
			
			try {
				con.rollback();
			} catch (SQLException e) {
				System.out.println("rollback");
				e.printStackTrace();
			}
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.setAutoCommit(true);
					con.close();

			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	
	/**
	 * <pre>
	 * 	근태마감 DELETE
	 * 	마감테이블에서 데이터를 삭제한다.
	 * </pre>
	 * @throws BaseException
	 * @author 	Yang D
	 * @since	2009.04.15
	 */
	public void deleteFinishDT(String updateArgs) throws BaseException {
		
		try {
			
			String temp_yyyymmdd = "";	//마감취소시 휴가 재배정 시작일
			String temp_now_dt = "";	//마감취소시 휴가 재배정 종료일
			
			con = DBConnection.getDataSource().getConnection();
			con.setAutoCommit(false);
			
			// 1. 마감취소 하기전 체크된 YYYYMMDD값중 가장 과거값을 가져온다.
			//    또한 현재 일자도 가져온다.
			sql.setLength(0);
			
			sql.append("\n").append(" SELECT TOP 1 YYYYMMDD AS YYYYMMDD, CONVERT(VARCHAR(8),GETDATE(),112) AS NOW_DT ");
			sql.append("\n").append(" FROM TB_ATTEND ");
			sql.append("\n").append(" WHERE YYYYMMDD IN (" + updateArgs + ") ");
			sql.append("\n").append(" GROUP BY YYYYMMDD ");
			sql.append("\n").append(" ORDER BY YYYYMMDD ASC ");
			
			System.out.println("======================================\r\n");
			System.out.println(" 1. "+sql.toString());
			System.out.println("======================================\r\n");
			
			pstmt = con.prepareStatement(sql.toString());
			
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				temp_yyyymmdd = rs.getString("YYYYMMDD");
				temp_now_dt = rs.getString("NOW_DT");
			}
			
			// 2. 과거일부터 현재일자까지 TB_VACATION에 휴가 배정처리된 모든 정보를 N으로 UPDATE 시킨다.
			sql.setLength(0);
			
			sql.append("\n").append(" UPDATE TB_VACATION ");
			sql.append("\n").append(" SET BATCH_FINISH_YN='N' ");
			sql.append("\n").append(" WHERE SEQ IN ( ");
			sql.append("\n").append(" 	SELECT SEQ FROM TB_VACATION ");
			sql.append("\n").append(" 	WHERE VACATION_TP='2001' ");
			sql.append("\n").append(" 	AND JOB_TYPE='BATCH' ");
			sql.append("\n").append(" 	AND BATCH_FINISH_YN='Y' ");
			sql.append("\n").append(" 	AND ALLOC_DT BETWEEN '"+temp_yyyymmdd+"' AND '"+temp_now_dt+"' ");
			sql.append("\n").append(" ) ");
			
			System.out.println("======================================\r\n");
			System.out.println(" 2. "+sql.toString());
			System.out.println("======================================\r\n");
			
			pstmt = con.prepareStatement(sql.toString());
			
			pstmt.execute();

			// 3. 마감취소시 마감된 일자를 삭제한다.
			sql.setLength(0);
			
			sql.append("                     DELETE FROM TB_ATTEND_CLOSE                           \n");
			sql.append("                     WHERE CLOSE_DT IN ( " + updateArgs + " )             \n");
			
			System.out.println("======================================\r\n");
			System.out.println(" 3. "+sql.toString());
			System.out.println("======================================\r\n");
			
			pstmt = con.prepareStatement(sql.toString());
			
			pstmt.execute();
			
			// 4. 근태정보를 UPDATE 한다. 이유는 모르겠다.
			sql.setLength(0);
			
			/*############################################################################################
			 * 수정사항 : query 변경 (마감취소시 data 와 확인을 풀면 안된다는 요청)
			 * 날짜 : 2011.09.01			 * 
			 * 수정자 : 최병옥
			 * 
			sql.append("                     UPDATE 	TB_ATTEND                           		\n");
			sql.append("                     SET 		ETC_MGR			=	NULL					\n");
			sql.append("                     ,			APPROV_EMP_NO	=	NULL					\n");
			sql.append("                     ,			CONFIRM_YN	=	'N'      					\n");
			sql.append("                     WHERE YYYYMMDD IN ( " + updateArgs + " )             	\n");
			sql.append("                     AND APPROV_EMP_NO = '1'					           	\n");
			
			System.out.println("======================================\r\n");
			System.out.println(" 4. "+sql.toString());
			System.out.println("======================================\r\n");
			
			pstmt = con.prepareStatement(sql.toString());
			
			pstmt.execute();
			
			con.commit();
			 ############################################################################################*/
			
		} catch (SQLException se) {
			
			try {
				con.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.setAutoCommit(true);
				con.close();
				
			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}




	/************************** GETTER / SETTER START *****************************/
	
	public String getMsg() {
		return msg;
	}

	public void setMsg(String msg) {
		this.msg = msg;
	}
	
	public List<HashMap<String, String>> getList() {
		return list;
	}

	public void setList(List<HashMap<String, String>> list) {
		this.list = list;
	}

	public List<String> getListCondition() {
		return listCondition;
	}

	public void setListCondition(List<String> listCondition) {
		this.listCondition = listCondition;
	}
	
	//휴가입력에서 갯수에 따라 다르게 저장됨. 20090120,20090121,20090122
	public void setYyyymmdd(String yyyymmdd) {
		this.yyyymmdd = yyyymmdd;
	}

	public String getYyyymmdd() {
		return yyyymmdd;
	}

	public String getChk_in_msg() {
		return chk_in_msg;
	}

	public void setChk_in_msg(String chk_in_msg) {
		this.chk_in_msg = chk_in_msg;
	}

	public String getChk_out_msg() {
		return chk_out_msg;
	}

	public void setChk_out_msg(String chk_out_msg) {
		this.chk_out_msg = chk_out_msg;
	}

	public int getRowCount() {
		return rowCount;
	}

	public void setRowCount(int rowCount) {
		this.rowCount = rowCount;
	}

	/**
	 * @return the close_dt
	 */
	public String getClose_dt() {
		return close_dt;
	}

	/**
	 * @param close_dt the close_dt to set
	 */
	public void setClose_dt(String close_dt) {
		this.close_dt = close_dt;
	}


	/************************** GETTER / SETTER E N D ******************************/
}
